<?php
if (! defined ( 'BASEPATH' ))
	exit ( 'No direct script access allowed' );
class Function_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
		//$this->output->enable_profiler(true);
	}
	
	function login($user, $password) {
		$this->db->where ( "account_user", $user );
		$this->db->where ( "password", $password );
		$query = $this->db->get ( "tbl_superadmin" );
		if ($query->num_rows () > 0) {
			foreach ( $query->result () as $rows ) {
				// add all data to session
				$newdata = array (
					'admin_id' => $rows->superadmin_id,
					'admin_name' => $rows->account_user,
					'admin_rule' => $rows->rule,
						// 'user_company' => $rows->company_id,
					'logged_in' => TRUE 
				);
			}
			$this->session->set_userdata ( $newdata );
			return true;
		}
		return false;
	}
	
	
	function login_user($user, $password) {
		$this->db->where ( "account_user", $user );
		$this->db->where ( "password", $password );
		$query = $this->db->get ( "tbl_admin" );
		if ($query->num_rows () > 0) {
			foreach ( $query->result () as $rows ) {
				// add all data to session
				$newdata = array (
					'user_id' => $rows->id,
					'user_name' => $rows->account_user,
					'user_rule' => $rows->rule,
					'user_company' => $rows->company_id,
					'logged_user' => TRUE
				);
			}
			$this->session->set_userdata ( $newdata );
			return true;
		}
		return false;
	}
	
	
	function get_member_by_id($id) {
		$this->db->select ( "*" );
		$this->db->from ( "tbl_end_user" );
		$this->db->where ( "end_user_id = ", $id );
		return $this->db->get ()->row ();
	}
	
	
	public function add_user() {
		$query = $this->db->query ( "SELECT * FROM tbl_company WHERE province_id=" . $this->input->post ( 'province' ) . " AND district_id=" . $this->input->post ( 'district' ) . "" );
		$query_sum = $query->row ();
		// /////////// Not Matching ////////////////
		if ($query->num_rows () <= 0) {
			// ////// Select count from tbl_queue (+1) /////////////
			$max_id_query = $this->db->query ( "SELECT MAX(queue_id) AS max_id FROM tbl_queue" );
			$max_id = $max_id_query->row ();
			$max_temp = $max_id->max_id;
			if ($max_temp == NULL) {
				$max_sum = 0 + 1;
			} else {
				$com_id_query = $this->db->query ( "SELECT company_id FROM tbl_queue WHERE queue_id = " . $max_temp . "" );
				$com_id = $com_id_query->row ();
				$max_sum = $com_id->company_id + 1;
			}
				// ////// Select company_id Maching with count(tbl_queue)+1 /////////////
				$company_query = $this->db->query ( "SELECT * FROM tbl_company WHERE company_id=" . $max_sum . "" );
				$company = $company_query->row ();
				if($company_query->num_rows ()<=0){
					$company_query1 = $this->db->query ( "SELECT MIN(company_id) AS mincom_id FROM tbl_company" );
					$company1 = $company_query1->row ();
					$com_id=$company1->mincom_id;
				}else{
					$com_id=$company->company_id;
				}
			$data = array (
					'company_id' => $com_id,
					'created_date' => date ( 'Y-m-d H:i:s' )
			);
			$this->db->insert ( 'tbl_queue', $data );
			// /////// Add Data to tbl_end_user //////////
			$data1 = array (
				'name' => $this->input->post ( 'name' ),
				'tel_number' => $this->input->post ( 'tel_number1' )."-".$this->input->post ( 'tel_number2' )."-".$this->input->post ( 'tel_number3' ),
				'fax' => $this->input->post ( 'fax1' )."-".$this->input->post ( 'fax2' )."-".$this->input->post ( 'fax3' ),
				'email' => $this->input->post ( 'email' ),
				'company_id' => $com_id,
				'province_id' => $this->input->post ( 'province' ),
				'district_id' => $this->input->post ( 'district' ),
				'address' => $this->input->post ( 'address' ),
				'meet_date' => $this->input->post ( 'meet_date' ),
				'created_date' => date ( 'Y-m-d H:i:s' ) 
			);
			$this->db->insert ( 'tbl_end_user', $data1 );
			// /////////// Matching ////////////////
		} else {
			$percentSM = 0.6;
			
			$rand = rand ( 0, 100 );
			$rand = $rand / 100.0;
			// /////////// Randon less than 60 per ////////////////
			if ($rand < $percentSM) {
				// ////// Select count from tbl_queue (+1) /////////////
				$max_id_query = $this->db->query ( "SELECT MAX(queue_id) AS max_id FROM tbl_queue" );
				$max_id = $max_id_query->row ();
				$max_temp = $max_id->max_id;
				if ($max_temp == NULL) {
					$max_sum = 0 + 1;
				} else {
					$com_id_query = $this->db->query ( "SELECT company_id FROM tbl_queue WHERE queue_id = " . $max_temp . "" );
					$com_id = $com_id_query->row ();
					$max_sum = $com_id->company_id + 1;
				}
				// ////// Select company_id Maching with count(tbl_queue)+1 /////////////
				$company_query = $this->db->query ( "SELECT * FROM tbl_company WHERE company_id=" . $max_sum . "" );
				$company = $company_query->row ();
				if($company_query->num_rows ()<=0){
					$company_query1 = $this->db->query ( "SELECT MIN(company_id) AS mincom_id FROM tbl_company" );
					$company1 = $company_query1->row ();
					$com_id=$company1->mincom_id;
				}else{
					$com_id=$company->company_id;
				}
				$data = array (
					'company_id' => $com_id,
					'created_date' => date ( 'Y-m-d H:i:s' )
				);
				$this->db->insert ( 'tbl_queue', $data );
				// /////// Add Data to tbl_end_user //////////
				$data1 = array (
					'name' => $this->input->post ( 'name' ),
					'tel_number' => $this->input->post ( 'tel_number1' )."-".$this->input->post ( 'tel_number2' )."-".$this->input->post ( 'tel_number3' ),
					'fax' => $this->input->post ( 'fax1' )."-".$this->input->post ( 'fax2' )."-".$this->input->post ( 'fax3' ),
					'email' => $this->input->post ( 'email' ),
					'company_id' => $com_id,
					'province_id' => $this->input->post ( 'province' ),
					'district_id' => $this->input->post ( 'district' ),
					'address' => $this->input->post ( 'address' ),
					'meet_date' => $this->input->post ( 'meet_date' ),
					'created_date' => date ( 'Y-m-d H:i:s' ) 
				);
				$this->db->insert ( 'tbl_end_user', $data1 );
						// /////////// Randon more than 60 per ////////////////
			} else if ($rand > $percentSM) {
				// /////// Add Data to tbl_end_user //////////
				$data = array (
					'name' => $this->input->post ( 'name' ),
					'tel_number' => $this->input->post ( 'tel_number1' )."-".$this->input->post ( 'tel_number2' )."-".$this->input->post ( 'tel_number3' ),
					'fax' => $this->input->post ( 'fax1' )."-".$this->input->post ( 'fax2' )."-".$this->input->post ( 'fax3' ),
					'email' => $this->input->post ( 'email' ),
					'company_id' => $query_sum->company_id,
					'province_id' => $this->input->post ( 'province' ),
					'district_id' => $this->input->post ( 'district' ),
					'address' => $this->input->post ( 'address' ),
					'meet_date' => $this->input->post ( 'meet_date' ),
					'created_date' => date ( 'Y-m-d H:i:s' ) 
				);
				$this->db->insert ( 'tbl_end_user', $data );
			}
		}
	}
	
	
	public function add_company() {
		if($this->input->post ( 'province' )==0 || $this->input->post ( 'district' )==0){
			$stat_pay=0;
		}else{
			$stat_pay=1;
		}
		$data = array (
			'name' => $this->input->post ( 'name' ),
			'email' => $this->input->post ( 'email' ),
			'tel_number' => $this->input->post ( 'tel_number1' )."-".$this->input->post ( 'tel_number2' )."-".$this->input->post ( 'tel_number3' ),
			'fax' => $this->input->post ( 'fax1' )."-".$this->input->post ( 'fax2' )."-".$this->input->post ( 'fax3' ),
			'address' => $this->input->post ( 'address' ),
			'province_id' => $this->input->post ( 'province' ),
			'district_id' => $this->input->post ( 'district' ),
			'status' => $stat_pay,
			'created_date' => date ( 'Y-m-d H:i:s' ) 
		);
		$this->db->insert ( 'tbl_company', $data );
		
		$com_id = $this->db->insert_id ();
		$data_dis1 = array (
			'district_status' => 1
		);
		$this->db->where('district_ID',  $this->input->post ( 'district' ));
		$this->db->update ( 'tbl_district', $data_dis1 );

		$data1 = array (
			'account_user' => $this->input->post ( 'account_user' ),
			'password' => $this->input->post ( 'password' ),
			'contact_name' => $this->input->post ( 'con_name' ),
			'contact_number' => $this->input->post ( 'con_tel1' )."-".$this->input->post ( 'con_tel2' )."-".$this->input->post ( 'con_tel3' ),
			'company_id' => $com_id 
		);
		$this->db->insert ( 'tbl_admin', $data1 );
		
		for($i = 0; $i < $this->input->post ( 'HIDnum_row' ); $i ++) {
			$data2 = array (
				'company_id' => $com_id,
				'province_id' => $_POST ['subprovince'] [$i],
				'district_id' => $_POST ['subdistrict'] [$i],
				'construction_area_details' => $_POST ['area_detail'] [$i]
			);
			$this->db->insert ( 'tbl_company_construction', $data2 );
			
			$data_dis2 = array (
				'district_ID' => $_POST ['subdistrict'] [$i],
				'district_status' => 1
			);
			$this->db->where('district_ID',  $_POST ['subdistrict'] [$i]);
			$this->db->update ( 'tbl_district', $data_dis2 );
		}
		
	}
	
	
	
	public function province_add() {
		$data = array (
			'PROVINCE_NAME' => $this->input->post ( 'name' ),
			'province_status' => 0		
		);
		$this->db->insert ( 'tbl_province', $data );
	}
	
	
	public function province_edit() {
		$data = array (
			'PROVINCE_NAME' => $this->input->post ( 'name' )	
		);
			$this->db->where('PROVINCE_ID',  $this->input->post ( 'province_id' ));
			$this->db->update ( 'tbl_province', $data );
	}
	
	
	function province_delete($id){
		$this->db->where('PROVINCE_ID', $id);
		$this->db->delete('tbl_province');
	}
	
	function province_delete_all(){
		$check = $this->input->post('chkDel');
		if($check != ""){
			foreach ($check as $index){
				$this->db->where('PROVINCE_ID', $index);
				$this->db->delete('tbl_province');
			}
		}	
	}
	
	
	public function district_add() {
		$data = array (
			'district_NAME' => $this->input->post ( 'name' ),
			'PROVINCE_ID' => $this->input->post ( 'province' )		
		);
		$this->db->insert ( 'tbl_district', $data );
	}
	
	
	public function district_edit() {
		$data = array (
			'district_NAME' => $this->input->post ( 'name' ),
			'PROVINCE_ID' => $this->input->post ( 'province' )		
		);
		$this->db->where('district_ID',  $this->input->post ( 'district_id' ));
		$this->db->update ( 'tbl_district', $data );
	}
	
	function district_delete($id){
		$this->db->where('district_ID', $id);
		$this->db->delete('tbl_district');
	}
	
	function district_delete_all(){
		$check = $this->input->post('chkDel');
		if($check != ""){
			foreach ($check as $index){
				$this->db->where('district_ID', $index);
				$this->db->delete('tbl_district');
			}
		}
	}
	
	public function edit_company() {
		$data = array (
			'name' => $this->input->post ( 'name' ),
			'email' => $this->input->post ( 'email' ),
			'tel_number' => $this->input->post ( 'tel_number1' )."-".$this->input->post ( 'tel_number2' )."-".$this->input->post ( 'tel_number3' ),
			'fax' => $this->input->post ( 'fax1' )."-".$this->input->post ( 'fax2' )."-".$this->input->post ( 'fax3' ),
			'address' => $this->input->post ( 'address' )	
		);
		$this->db->where('company_id', $this->input->post ( 'company_id' ));
		$this->db->update ( 'tbl_company', $data );
	
		$data1 = array (
			'account_user' => $this->input->post ( 'account_user' ),
			'contact_name' => $this->input->post ( 'con_name' ),
			'contact_number' => $this->input->post ( 'con_tel1' )."-".$this->input->post ( 'con_tel2' )."-".$this->input->post ( 'con_tel3' ),
		);
		$this->db->where('company_id', $this->input->post ( 'company_id' ));
		$this->db->update ( 'tbl_admin', $data1 );
	}
	
	
	public function edit_company_user() {
		$data = array (
			'email' => $this->input->post ( 'email' ),
			'tel_number' => $this->input->post ( 'tel_number1' )."-".$this->input->post ( 'tel_number2' )."-".$this->input->post ( 'tel_number3' ),
			'fax' => $this->input->post ( 'fax1' )."-".$this->input->post ( 'fax2' )."-".$this->input->post ( 'fax3' ),
			'address' => $this->input->post ( 'address' )
		);
		$this->db->where('company_id', $this->input->post ( 'company_id' ));
		$this->db->update ( 'tbl_company', $data );
	
		$data1 = array (
			'contact_name' => $this->input->post ( 'con_name' ),
			'contact_number' => $this->input->post ( 'con_tel1' )."-".$this->input->post ( 'con_tel2' )."-".$this->input->post ( 'con_tel3' )
		);
		$this->db->where('company_id', $this->input->post ( 'company_id' ));
		$this->db->update ( 'tbl_admin', $data1 );
	}
	
	
	function company_delete($id){
		$this->db->where ( "company_id", $id );
		$query = $this->db->get ( "tbl_company" );
		foreach ( $query->result () as $rows ) {
			$data = array ('district_status' => 0);
			$this->db->where('district_ID', $rows->district_id);
			$this->db->update ( 'tbl_district', $data );
		}
			$this->db->where ( "company_id", $id );
			$query1 = $this->db->get ( "tbl_company_construction" );
			foreach ( $query1->result () as $construc ) {
				$data1 = array ('district_status' => 0);
				$this->db->where('district_ID', $construc->district_id);
				$this->db->update ( 'tbl_district', $data1 );
			}
			$this->db->where('company_id', $id);
			$this->db->delete('tbl_company');
			
			$this->db->where('company_id', $id);
			$this->db->delete('tbl_company_construction');
			
			$this->db->where('company_id', $id);
			$this->db->delete('tbl_admin');
			
				$max_id_query = $this->db->query ( "SELECT MAX(queue_id) AS max_id FROM tbl_queue" );
				$max_id = $max_id_query->row ();
				$max_temp = $max_id->max_id;
				
					$com_id_query = $this->db->query ( "SELECT company_id FROM tbl_queue WHERE queue_id = " . $max_temp . "" );
					$com_id = $com_id_query->row ();
					if($com_id->company_id==$id){
						$max_sum = $com_id->company_id + 1;
					}else{ 
						$max_sum = $com_id->company_id;
					}
				// ////// Select company_id Maching with count(tbl_queue)+1 /////////////
				$company_query = $this->db->query ( "SELECT * FROM tbl_company WHERE company_id=" . $max_sum . "" );
				$company = $company_query->row ();
				if($company_query->num_rows ()<=0){
					$company_query1 = $this->db->query ( "SELECT MIN(company_id) AS mincom_id FROM tbl_company" );
					$company1 = $company_query1->row ();
					$com_id=$company1->mincom_id;
				}else{
					$com_id=$company->company_id;
				}
			$data2 = array ('company_id' => $com_id);
			$this->db->where('company_id', $id);
			$this->db->update ( 'tbl_end_user', $data2 );
	}
	
	
	function company_delete_all(){	
		$check = $this->input->post('chkDel');
		if($check != ""){
			foreach ($check as $index){
				$this->db->where ( "company_id", $index );
				$query = $this->db->get ( "tbl_company" );
				foreach ( $query->result () as $rows ) {
					$data = array ('district_status' => 0);
					$this->db->where('district_ID', $rows->district_id);
					$this->db->update ( 'tbl_district', $data );
				}
				$this->db->where ( "company_id", $index );
				$query1 = $this->db->get ( "tbl_company_construction" );
				foreach ( $query1->result () as $construc ) {
					$data1 = array ('district_status' => 0);
					$this->db->where('district_ID', $construc->district_id);
					$this->db->update ( 'tbl_district', $data1 );
				}
				$this->db->where('company_id', $index);
				$this->db->delete('tbl_company');
					
				$this->db->where('company_id', $index);
				$this->db->delete('tbl_company_construction');
					
				$this->db->where('company_id', $index);
				$this->db->delete('tbl_admin');
					
				$max_id_query = $this->db->query ( "SELECT MAX(queue_id) AS max_id FROM tbl_queue" );
				$max_id = $max_id_query->row ();
				$max_temp = $max_id->max_id;
				
				$com_id_query = $this->db->query ( "SELECT company_id FROM tbl_queue WHERE queue_id = " . $max_temp . "" );
				$com_id = $com_id_query->row ();
				if($com_id->company_id==$index){
					$max_sum = $com_id->company_id + 1;
				}else{
					$max_sum = $com_id->company_id;
				}
				// ////// Select company_id Maching with count(tbl_queue)+1 /////////////
				$company_query = $this->db->query ( "SELECT * FROM tbl_company WHERE company_id=" . $max_sum . "" );
				$company = $company_query->row ();
				if($company_query->num_rows ()<=0){
					$company_query1 = $this->db->query ( "SELECT MIN(company_id) AS mincom_id FROM tbl_company" );
					$company1 = $company_query1->row ();
					$com_id=$company1->mincom_id;
				}else{
					$com_id=$company->company_id;
				}
				$data2 = array ('company_id' => $com_id);
				$this->db->where('company_id', $index);
				$this->db->update ( 'tbl_end_user', $data2 );
			}
		}
	}
	
	
	function select_province($id){		
		$query = $this->db->query ( 'select * from tbl_province WHERE PROVINCE_ID='.$id.'' );		
		return $query->result ();
	}
	
	
	function select_district($id){
		$query = $this->db->query ( 'select * from tbl_district WHERE district_ID='.$id.'' );
		return $query->result ();
	}
	
	
	function getProvince() {
		/*
		 * $query = $this->db->get('location'); foreach ($query->result() as $row) { echo $row->description; }
		 */
		$query = $this->db->query ( 'select * from tbl_province Order By PROVINCE_ID ASC' );
		return $query->result ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	function getDistrict() {
		/*
		 * $query = $this->db->get('location'); foreach ($query->result() as $row) { echo $row->description; }
		*/
		$query = $this->db->query ( 'select d.*,p.PROVINCE_NAME from tbl_district d , tbl_province p WHERE d.province_id = p.PROVINCE_ID Order By d.province_id ASC' );
		return $query->result ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	function getCompany() {
		/*
		 * $query = $this->db->get('location'); foreach ($query->result() as $row) { echo $row->description; }
		 */
		$query = $this->db->query ( 'select * from tbl_company Order By company_id DESC' );
		return $query->result ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	function manage_end_user() {
		/*
		 * $query = $this->db->get('location'); foreach ($query->result() as $row) { echo $row->description; }
		*/
		$query = $this->db->query ( 'select e.*,c.name as com_name from tbl_end_user e,tbl_company c WHERE e.company_id = c.company_id Order By c.company_id DESC' );
		return $query->result ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	public function end_user_edit() {
		$data = array (
			'note_comment' => $this->input->post ( 'note_comment' )
					
		);
		$this->db->where('end_user_id',  $this->input->post ( 'company_id' ));
		$this->db->update ( 'tbl_end_user', $data );
	}
	
	
	public function end_user_delete($id) {
		$this->db->where('end_user_id', $id);
		$this->db->delete('tbl_end_user');
	}
	
	
	public function end_user_delete_all() {
		$check = $this->input->post('chkDel');
		if($check != ""){
			foreach ($check as $index){
				$this->db->where('end_user_id', $index);
				$this->db->delete('tbl_end_user');
			}
		}
	}
	
	
	function get_end_user($id) {
		/*
		 * $query = $this->db->get('location'); foreach ($query->result() as $row) { echo $row->description; }
		*/
		$query = $this->db->query ( 'select e.*,c.name as com_name,p.PROVINCE_NAME,d.district_NAME from tbl_end_user e,tbl_company c,tbl_province p,tbl_district d WHERE e.province_id = p.PROVINCE_ID AND e.district_id=d.district_ID AND e.company_id = c.company_id AND e.end_user_id = '.$id.' Order By c.company_id DESC' );
		return $query->result ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	function getCompanyInfo($id) {
		/*
		 * $query = $this->db->get('location'); foreach ($query->result() as $row) { echo $row->description; }
		*/
		$query = $this->db->query ( 'select c.*,a.*,p.PROVINCE_NAME,d.district_NAME from tbl_company c , tbl_admin a,tbl_province p , tbl_district d WHERE c.company_id = a.company_id AND c.province_id = p.PROVINCE_ID AND c.district_id = d.district_ID AND c.company_id = '.$id.'' );
		return $query->result ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	function getBranchCompany($id) {
		$query = $this->db->query ( 'SELECT * FROM tbl_company_construction,tbl_province,tbl_district WHERE tbl_company_construction.province_id = tbl_province.PROVINCE_ID AND tbl_company_construction.district_id = tbl_district.district_ID AND tbl_company_construction.company_id = '.$id.'' );
		return $query->result ();
	}
	
	
	function getCompanyEmail($id) {
		$this->db->select ( "tbl_company.*,tbl_admin.*,tbl_province.PROVINCE_NAME,tbl_district.district_NAME" );
		$this->db->from ( "tbl_company" );
		$this->db->join('tbl_admin', 'tbl_company.company_id = tbl_admin.company_id');
		$this->db->join('tbl_province', 'tbl_company.province_id = tbl_province.PROVINCE_ID');
		$this->db->join('tbl_district', 'tbl_company.district_id = tbl_district.district_ID');
		$this->db->where ( "tbl_company.company_id = ", $id );
		return $this->db->get ()->row ();
		// echo 'Total Results: ' . $query->num_rows();
	}
	
	
	function update_status_email($id) {
		$com_id_query = $this->db->query ( "SELECT * FROM tbl_company WHERE company_id = " . $id . "" );
		$com_id = $com_id_query->row ();
		$data = array (
			'send_email_status' => $com_id->send_email_status+1
			);
		$this->db->where('company_id', $id);
		$this->db->update ( 'tbl_company', $data );
	}
	
	
}
?>